package com.huangpuweb.api.service.imp;

import com.alibaba.fastjson.JSONObject;
import com.huangpuweb.api.model.*;
import com.huangpuweb.api.service.LaoXiMenApiService;
import com.huangpuweb.api.util.DateUtil;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class LaoXiMenApiServiceImp extends BaseLaoXiMenDataServiceImp implements LaoXiMenApiService {


    @Override
    public List<LxmShopError> getListShopError() {
        String sql = "select * from sys_shop_error";
        return getEntryList(sql, new LxmShopErrorRowMapper(), new Object[]{});
    }

    @Override
    public LxmSignageError getLxmSignageError(String number) {
        String sql = "select * from sys_signage_error where number=?";
        return getEntry(sql, new LxmSignageErrorRowMapper(), new Object[]{number});
    }

    @Override
    public LxmShopError getErrorShopById(String shopid) {
        String sql = "select * from sys_shop_error where id=?";
        return getEntry(sql, new LxmShopErrorRowMapper(), new Object[]{shopid});
    }

    @Override
    public List<String> getListNumberByType(String type) {
        String sql = "";
        if (type.equals("2")) {
            sql = "select distinct number from sys_signage_error";
            return queryStringArray(sql, new Object[]{});
        } else {
            sql = "select distinct number from sys_signage_error where type=?";
            return queryStringArray(sql, new Object[]{type});
        }


    }

    @Override
    public LxmShopError getErrorShopByNumber(String number) {
        String sql = "select * from sys_shop_error where number=?";
        return getEntry(sql, new LxmShopErrorRowMapper(), new Object[]{number});
    }

    @Override
    public List<LxmSignageError> getListLxmSignageError(String number) {
        String sql = "select * from sys_signage_error where number=?";
        return getEntryList(sql, new LxmSignageErrorRowMapper(), new Object[]{number});
    }

    @Override
    public List<SysHpPublicVideoStreet> getRoadIdsFromHpPublicVideoByStreet(int page, Integer goodspagesize) {
        StringBuffer sbBuffer = new StringBuffer();
        List<Object> params = new ArrayList<Object>();
        String sql = "select * from sys_video  ";
        return getEntryList(sql + sbBuffer.toString(), new SysHpPublicVideoStreetRowMapper(), params.toArray());

    }

    @Override
    public List<SysShopRecruitStreet> getAllHpDianZhaoDianPaiInfo(String page, String key, Integer offset) {
        Integer curr = Integer.valueOf(page);
        if (!key.equals("")) {
            String sql = "select * from sys_shop_recruit_copy1 where (unit_adress like ? or detail like ?) limit ? offset ?";
            return getEntryList(sql, new SysShopRecruitStreetRowMapper(), new Object[]{"%" + key + "%", "%" + key + "%", offset, (curr - 1) * offset});
        } else {
            String sql = "select * from sys_shop_recruit_copy1   limit ? offset ?";
            return getEntryList(sql, new SysShopRecruitStreetRowMapper(), new Object[]{offset, (curr - 1) * offset});
        }

    }

    @Override
    public void updateSysShopRecruitStreetLngLatById(String lng, String lat, int id) {
        String sql = "update sys_shop_recruit set lng=?,lat=? where id=?";
        update(sql, new Object[]{lng, lat, id});
    }

    @Override
    public List<SysShopSignageStreet> getListSysShopSignageStreetByNumber(String serial_number) {
        String sql = "select * from sys_shop_signage where number=?";
        return getEntryList(sql, new SysShopSignageStreetRowMapper(), new Object[]{serial_number});
    }

    @Override
    public SysShopRecruitStreet getSysShopRecruitStreetByShopId(int shopid) {
        String sql = "select * from sys_shop_recruit where id=?";
        return getEntry(sql, new SysShopRecruitStreetRowMapper(), new Object[]{shopid});
    }

    @Override
    public Integer getLxmShopCount() {
        String sql = "select count(1) from sys_shop_recruit";
        return queryInteger(sql, new Object[]{});
    }

    @Override
    public Integer getLxmShopSignageCount() {
        String sql = "select count(1) from sys_shop_signage";
        return queryInteger(sql, new Object[]{});
    }

    @Override
    public List<SysHpXiaoFangAjType> getLxmShopTypeCount() {
        String sql = "select categary as name,count(1) as count from sys_shop_recruit group by categary";
        return getEntryList(sql, new SysHpXiaoFangAjTypeRowMapper(), new Object[]{});
    }

    @Override
    public List<LxmSuperviseStrength> getListLxmSuperviseStrength() {
        String sql = "select * from sys_supervise_strength";
        return getEntryList(sql, new LxmSuperviseStrengthRowMapper(), new Object[]{});
    }

    @Override
    public List<SysHpXiaoFangAjType> getLxmShopSignage() {
        String sql = "select spec as name,count(1) as count from sys_shop_recruit group by spec";
        return getEntryList(sql, new SysHpXiaoFangAjTypeRowMapper(), new Object[]{});
    }

    @Override
    public List<SysHpXiaoFangAjType> getLxmShopRecruitDzAffiliation() {
        String sql = "select dz_affiliation as name,count(1) as count from sys_shop_recruit group by dz_affiliation";
        return getEntryList(sql, new SysHpXiaoFangAjTypeRowMapper(), new Object[]{});
    }

    @Override
    public List<String> getListRoad() {
        String sql = "select road_name from  sys_shop_recruit group by road_name";
        return queryStringArray(sql, new Object[]{});
    }

    @Override
    public List<SysShopRecruitStreet> getListShopByRoadName(String roadname) {
        String sql = "select * from sys_shop_recruit where road_name=?";
        return getEntryList(sql, new SysShopRecruitStreetRowMapper(), new Object[]{roadname});
    }

    @Override
    public SysShopRecruitStreet getSysShopRecruitStreetByShopName(String shopname) {
        String sql = "select * from sys_shop_recruit where unit_name like ?";
        return getEntry(sql, new SysShopRecruitStreetRowMapper(), new Object[]{"%" + shopname + "%"});
    }

    @Override
    public List<String> getListType() {
        String sql = "select status from  sys_shop_recruit group by status";
        return queryStringArray(sql, new Object[]{});
    }

    @Override
    public List<SysShopRecruitStreet> getListShopBytype(String type) {
        String sql = "select * from sys_shop_recruit where status=?";
        return getEntryList(sql, new SysShopRecruitStreetRowMapper(), new Object[]{type});
    }

    @Override
    public List<String> getListSysShopSignageStreetByspecification(String specification) {
        String sql = "select number from sys_shop_signage  where specification=? group by number ;";
        return queryStringArray(sql, new Object[]{specification});
    }

    @Override
    public SysShopRecruitStreet getLxmShopRecruitByNumber(String number) {
        String sql = "select * from sys_shop_recruit where serial_number=?";
        return getEntry(sql, new SysShopRecruitStreetRowMapper(), new Object[]{number});
    }

    @Override
    public Integer getLxmShopCountByType(String type) {
        String sql = "select count(1) from sys_shop_recruit where status=?";
        return queryInteger(sql, new Object[]{type});
    }

    @Override
    public Integer getLxmShopCountByRoad(String road) {
        //road_name
        String sql = "select count(1) from sys_shop_recruit where road_name=?";
        return queryInteger(sql, new Object[]{road});
    }

    @Override
    public List<SysShopRecruitStreet> getListShopByXiaotype(String type) {
        String sql = "select * from sys_shop_recruit where categary=?";
        return getEntryList(sql, new SysShopRecruitStreetRowMapper(), new Object[]{type});
    }

    @Override
    public String getLxmShopTypeByNumber(String number) {
        String sql = "select type from sys_signage_error  where number=?";
        return queryString(sql, new Object[]{number});
    }

    @Override
    public List<LxmElevatorInfo> getListLxmElevatorInfo() {
        String sql = "select * from elevator_info";
        return getEntryList(sql, new LxmElevatorInfoRowMapper(), new Object[]{});
    }

    @Override
    public void updateElevatorInfoLngLatById(String lng, String lat, Integer id) {
        String sql = "update elevator_info set lng=?,lat=? where id=?";
        update(sql, new Object[]{lng, lat, id});
    }

    @Override
    public List<SysHpPublicVideoStreet> getRoadIdsFromHpPublicVideoByStreetCondition(String level) {


        String sql = "select * from sys_video  where road_grade=?";
        return getEntryList(sql, new SysHpPublicVideoStreetRowMapper(), new Object[]{level});
    }

    @Override
    public List<SysHpPublicVideoStreet> getRoadIdsFromHpPublicVideoByStreetImportCondition() {
        String sql = "select * from sys_video  where point_region=?";
        return getEntryList(sql, new SysHpPublicVideoStreetRowMapper(), new Object[]{"是"});
    }

    @Override
    public List<LxmElevatorInfo> getAllElevatorInfo() {
        String sql = "select manage_unit,supervise_person,ywh_name,ywh_person,ywh_phone,juwei,id,device_code,use_status,test_date,test_result,next_test_date,next_selftest_date,device_stop_date,putuse_date,register_type,device_type,point_monitor,use_unit,lat,lng,device_use_site,device_use_place,checkout_duty_uint,safe_administrator,safe_administrator_phone,service_unit_name,service_unit_phone,manufacture_date,checkout_type,device_rank,old_device_status  from elevator_info ";
        return getEntryList(sql, new LxmElevatorInfoRowMapper(), new Object[]{});
    }

    @Override
    public List<LxmElevatorInfo> getAllElevatorOldInfo(String year) {
        String sql = "select manage_unit,supervise_person,ywh_name,ywh_person,ywh_phone,juwei,id,device_code,use_status,test_date,test_result,next_test_date,next_selftest_date,device_stop_date,putuse_date,register_type,device_type,point_monitor,use_unit,lat,lng,device_use_site,device_use_place,checkout_duty_uint,safe_administrator,safe_administrator_phone,service_unit_name,service_unit_phone,manufacture_date,checkout_type,device_rank,old_device_status  from elevator_info where  manufacture_date<?";
        return getEntryList(sql, new LxmElevatorInfoRowMapper(), new Object[]{year});

    }

    @Override
    public List<LxmElevatorExamineInfo> getLXMElevatorExamineByQuarter(String quarter) {
        String sql = "select * from elevator_examine where examine_quarter=?";
        return getEntryList(sql, new LxmElevatorExamineInfoRowMapper(), new Object[]{quarter});
    }

    @Override
    public List<LxmElevatorPunishUnit> getLXMPunishmentList() {
        String sql = "select * from elevator_punish_unit";
        return getEntryList(sql, new LxmElevatorPunishUnitRowMapper(), new Object[]{});
    }

    @Override
    public List<LxmElevatorRectifyUint> getLXMRectifyList() {
        String sql = "select id,unit_name,adress,discover_time,hidden_channel,hidden_detail,rectify_date,rectify_method,lng,lat from elevator_rectify_uint";
        return getEntryList(sql, new LxmElevatorRectifyUintRowMapper(), new Object[]{});
    }

    @Override
    public List<String> getShopRecruitImageList() {
        String sql = "select serial_number from sys_shop_recruit";
        return queryStringArray(sql, new Object[]{});
    }

    @Override
    public List<LxmElevatorInfo> getElevatorInfoByDeviceUsePlace(String deviceUsePlace) {
        String sql = "select manage_unit,supervise_person,ywh_name,ywh_person,ywh_phone,juwei,id,device_code,use_status,test_date,test_result,next_test_date,next_selftest_date,device_stop_date,putuse_date,register_type,device_type,point_monitor,use_unit,lat,lng,device_use_site,device_use_place,checkout_duty_uint,safe_administrator,safe_administrator_phone,service_unit_name,service_unit_phone,manufacture_date,checkout_type,device_rank,old_device_status  from elevator_info where device_use_place=? ";
        return getEntryList(sql, new LxmElevatorInfoRowMapper(), new Object[]{deviceUsePlace});
    }


    @Override
    public List<JuWeiPopulation> getJuWeiList() {
        String sql="select * from juwei_population";
        return  getEntryList(sql, new RowMapper<JuWeiPopulation>() {
            @Override
            public JuWeiPopulation mapRow(ResultSet resultSet, int i) throws SQLException {
                JuWeiPopulation jw = new JuWeiPopulation();
//                jw.setJWID(resultSet.getString("JWID"));
                jw.setJwmc(resultSet.getString("JWMC"));
                jw.setSsjd(resultSet.getString("SSJD"));
                jw.setPopulation(resultSet.getString("Population"));
                jw.setPoint_x(resultSet.getString("POINT_X"));
                jw.setPoint_y(resultSet.getString("POINT_Y"));
                return jw;
            }
        },new Object[]{});
    }

    @Override
    public List<SysXiaoGongCheng> getXiaoGongChengProjectByMysql() {
        String sql="select * from sys_xiaogongcheng";
        return getEntryList(sql, new RowMapper<SysXiaoGongCheng>() {
            @Override
            public SysXiaoGongCheng mapRow(ResultSet resultSet, int i) throws SQLException {
                SysXiaoGongCheng sc=new SysXiaoGongCheng();
                sc.set_id(resultSet.getString("_id"));
                sc.setState(resultSet.getString("state"));
                sc.setRefuse_text(resultSet.getString("refuse_text"));
                sc.setPrj_name(resultSet.getString("prj_name"));
                sc.setPrj_addr(resultSet.getString("prj_addr"));
                sc.setPrj_area(resultSet.getString("prj_area"));
                sc.setPrj_price(resultSet.getString("prj_price"));
                sc.setDemand_com(resultSet.getString("demand_com"));
                sc.setConstruction_com(resultSet.getString("construction_com"));
                sc.setPrj_state(resultSet.getString("prj_state"));
                sc.setCheck_depart(resultSet.getString("check_depart"));
                sc.setCheck_person(resultSet.getString("check_person"));
                sc.setCheck_content(resultSet.getString("check_content"));
                sc.setCheck_question(resultSet.getString("check_question"));
                sc.setCheck_question_desp(resultSet.getString("check_question_desp"));
                sc.setChange_state(resultSet.getString("change_state"));
                sc.setChange_desp(resultSet.getString("change_desp"));
                sc.setChange_pictures(resultSet.getString("change_pictures"));
                sc.setPrj_property(resultSet.getString("prj_property"));
                sc.setPrj_lease_contract(resultSet.getString("prj_lease_contract"));
                sc.setPrj_con_contract(resultSet.getString("prj_con_contract"));
                sc.setPrj_license(resultSet.getString("prj_license"));
                sc.setPrj_certifications(resultSet.getString("prj_certifications"));
                sc.setPrj_manager_cert(resultSet.getString("prj_manager_cert"));
                sc.setPrj_safe_cert(resultSet.getString("prj_safe_cert"));
                sc.setPrj_manager_appiontment(resultSet.getString("prj_manager_appiontment"));
                sc.setPrj_safe_appiontment(resultSet.getString("prj_safe_appiontment"));
                sc.setPrj_design_cert(resultSet.getString("prj_design_cert"));
                sc.setPrj_assist_org(resultSet.getString("prj_assist_org"));
                sc.setPrj_person_name(resultSet.getString("prj_person_name"));
                sc.setPrj_person_phone(resultSet.getString("prj_person_phone"));
                sc.setPrj_type(resultSet.getString("prj_type"));
                sc.setUserid(resultSet.getString("userid"));
                sc.setUpdateTime(resultSet.getString("updateTime"));
                sc.set__v(resultSet.getString("__v"));
                sc.setPrj_grid(resultSet.getString("prj_grid"));
                sc.setPicture(resultSet.getString("picture"));
                sc.setLocation(resultSet.getString("location"));
                sc.setPrj_depart(resultSet.getString("prj_depart"));
                return sc;
            }
        }, new Object[]{});
    }

    @Override
    public List<SysXiaoGongCheng> getXiaoGongChengByMysql() {
        String sql="select january,february,march,apr,may,june,july,august,september,october,november,december from sys_xiaogongcheng group by january,february,march,apr,may,june,july,august,september,october,november,december";
        return getEntryList(sql, new RowMapper<SysXiaoGongCheng>() {
            @Override
            public SysXiaoGongCheng mapRow(ResultSet resultSet, int i) throws SQLException {
                SysXiaoGongCheng sc=new SysXiaoGongCheng();
                sc.setJanuary(JSONObject.parseObject( resultSet.getString("january")));
                sc.setFebruary(JSONObject.parseObject(resultSet.getString("february")));
                sc.setMarch(JSONObject.parseObject(resultSet.getString("march")));
                sc.setApr(JSONObject.parseObject(resultSet.getString("apr")));
                sc.setMay(JSONObject.parseObject(resultSet.getString("may")));
                sc.setJune(JSONObject.parseObject(resultSet.getString("june")));
                sc.setJuly(JSONObject.parseObject(resultSet.getString("july")));
                sc.setAugust(JSONObject.parseObject(resultSet.getString("august")));
                sc.setSeptember(JSONObject.parseObject(resultSet.getString("september")));
                sc.setOctober(JSONObject.parseObject(resultSet.getString("october")));
                sc.setNovember(JSONObject.parseObject(resultSet.getString("november")));
                sc.setDecember(JSONObject.parseObject(resultSet.getString("december")));
                return sc;
            }
        }, new Object[]{});
    }

    @Override
    public String findHpDianZhaoDianPaiInfo(String serial_number) {
        String sql="select serial_number from sys_shop_recruit_copy1 where serial_number=?";
        return queryString(sql,new Object[]{serial_number});
    }

    @Override
    public String findSysSignageError(String number) {
        String sql="select number from sys_shop_error_copy1 where number=?";
        return queryString(sql,new Object[]{number});
    }

    @Override
    public int getSignageError(String address, Integer page, Integer offset) {
        if (address.equals("")) {
            String sql = "select count(1) from sys_shop_error_copy1   ";
            return queryInteger(sql, new Object[]{});
        } else {
            String sql = "select count(1)  from sys_signage_error_copy1 sr right join sys_shop_error_copy1 se on sr.number=se.number where se.address like ? ";
            return queryInteger(sql, new Object[]{"%" + address + "%"});
        }
    }

    @Override
    @Transactional
    public void updateExamineRecord(LxmSysExamineRecord le) {
        SimpleDateFormat sf=new SimpleDateFormat("yyyy-MM-dd");
        String format = sf.format(le.getExamine_date());
        //今天有数据修改
        if (format.equals(DateUtil.getDate()) && le.getFlag()) {
            String sql = "update  sys_examine_record  set examine_person=?,examine_detail=? where error_number=? and examine_date=? ";
            update(sql, new Object[]{le.getExamine_person(), le.getExamine_detail(), le.getError_number(),format});
        //今天无数据新增
        } else if (format.equals(DateUtil.getDate()) && !le.getFlag()) {
            String sql = "insert  into sys_examine_record  ( examine_date,examine_person,examine_detail,error_number)values(?,?,?,?) ";
            update(sql, new Object[]{le.getExamine_date(), le.getExamine_person(), le.getExamine_detail(), le.getError_number()});
        //今天以前日期无数据新增
        } else if (!format.equals(DateUtil.getDate())) {
            String sql = "insert into sys_examine_record  ( examine_date,examine_person,examine_detail,error_number)values(?,?,?,?) ";
            update(sql, new Object[]{le.getExamine_date(), le.getExamine_person(), le.getExamine_detail(), le.getError_number()});
        }
    }

    @Override
    public LxmSysUserAdmin findUserByUserName(String user_name) {
        String sql = "select * from sys_user_admin where user_name=?";
        return getEntry(sql, new LxmSysUserAdminRowMapper(), new Object[]{user_name});
    }

    @Override
    public Integer getShopCount(String key) {
        if (!key.equals("")) {
            String sql1 = "select count(1) from sys_shop_recruit_copy1 where (unit_adress like ? or unit_name like ?)";
            return queryInteger(sql1, new Object[]{"%" + key + "%", "%" + key + "%"});
        } else {
            String sql1 = "select count(1) from sys_shop_recruit_copy1";
            return queryInteger(sql1, new Object[]{});
        }
    }

    @Override
    public List<SysShopRecruitStreet> getListSysShopRecrutiStreet() {
        String sql = "select * from sys_shop_recruit";
        return getEntryList(sql, new SysShopRecruitStreetRowMapper(), new Object[]{});
    }

    @Override
    public int getCount() {
        String sql = "select count(1) from sys_shop_recruit";
        return queryInteger(sql, new Object[]{});
    }

    @Override
    public Integer getPageCount(String key) {
        if (!key.equals("")) {
            String sql = "select count(1) from sys_user_admin where unit like ?";
            return queryInteger(sql, new Object[]{"%" + key + "%"});
        } else {
            String sql = "select count(1) from sys_user_admin ";
            return queryInteger(sql, new Object[]{});
        }
    }

    @Override
    public List<LxmSysUserAdmin> findSysUserInfo(String key, int page, int offset) {
        if (!key.equals("")) {
            String sql = "select * from sys_user_admin where unit like ? limit ? offset ?";
            return getEntryList(sql, new LxmSysUserAdminRowMapper(), new Object[]{"%" + key + "%", offset, (page - 1) * offset});
        } else {
            String sql = "select * from sys_user_admin limit ? offset ?";
            return getEntryList(sql, new LxmSysUserAdminRowMapper(), new Object[]{offset, (page - 1) * offset});
        }
    }

    @Override
    public List<LxmSysExamineRecord> getExamineRecord(String error_number) {
        String sql = "select * from sys_examine_record where error_number=? order by examine_date desc  ";
        return getEntryList(sql, new LxmSysExamineRecordRowMapper(), new Object[]{error_number});
    }

    @Override
    @Transactional
    public void deleteSysUserInfo(LxmSysUserAdmin ls) {
        String sql = "delete from sys_user_admin where id=?";
        update(sql, new Object[]{ls.getId()});
    }

    @Override
    @Transactional
    public void updateSysUserInfo(LxmSysUserAdmin ls) {
        String sql = "update sys_user_admin set user_name=?,name=?,unit=?,phone=?,user_qx=? where id=?";
        update(sql, new Object[]{ls.getUser_name(), ls.getName(), ls.getUnit(), ls.getPhone(), ls.getUser_qx(), ls.getId()});
    }

    @Override
    @Transactional
    public void addSysUserInfo(LxmSysUserAdmin ls) {
        String sql = "insert into sys_user_admin (password,user_name,name,unit,phone,user_qx) values(?,?,?,?,?,?)";
        update(sql, new Object[]{ls.getPassword(), ls.getUser_name(), ls.getName(), ls.getUnit(), ls.getPhone(), ls.getUser_qx()});
    }

    @Override
    @Transactional
    public void deleteSysSignageError(LxmSignageError ls) {
        String sql = "delete from sys_signage_error_copy1 where number=?";
        update(sql, new Object[]{ls.getNumber()});
        String sql1 = "delete from sys_shop_error_copy1 where number=?";
        update(sql1, new Object[]{ls.getNumber()});
    }

    @Override
    @Transactional
    public void updateSysSignageError(LxmShopError ls) {
        String sql = "update sys_shop_error_copy1 set " +
                "rectify_number=?,address=?,unit_name=?,name=?,phone=?,status=? where number=?";
        update(sql, new Object[]{ls.getRectify_number(), ls.getAddress(), ls.getUnit_name(), ls.getName(), ls.getPhone(), ls.getStatus(), ls.getNumber()});
        String sql1 = "update sys_signage_error_copy1 set " +
                "examine_date=?,review_result=?,review_date=?,hidden_danger_desc=? where number=?";
        update(sql1, new Object[]{ls.getExamine_date(), ls.getReview_result(), ls.getReview_date(), ls.getNumber(), ls.getHidden_danger_desc()});

    }

    @Override
    @Transactional
    public void addSysSignageError(LxmShopError ls) {
        String sql = "insert into sys_shop_error_copy1(number,rectify_number,address,unit_name,name,phone,status) " +
                "values(?,?,?,?,?,?,?)";
        update(sql, new Object[]{ls.getNumber(), ls.getRectify_number(), ls.getAddress(), ls.getUnit_name(), ls.getName(), ls.getPhone(), ls.getStatus()});
        String sql1 = "insert into sys_signage_error_copy1  (examine_date,type,review_date,review_result,number,hidden_danger_desc) values(?,?,?,?,?,?)";
        update(sql1, new Object[]{ls.getExamine_date(), ls.getType(), ls.getReview_date(), ls.getReview_result(), ls.getNumber(), ls.getHidden_danger_desc()});
    }

    @Override
    public List<LxmSignageError> getSysSignageErrorListByType(String address, Integer page, Integer offset) {
        if (address.equals("")) {
            String sql = "select sr.hidden_danger_desc,sr.type,sr.review_result,sr.examine_date,sr.review_date,se.address,se.number,se.rectify_number,se.unit_name,se.name,se.phone,se.status from sys_shop_error_copy1 se left join sys_signage_error_copy1 sr on sr.number=se.number limit ? offset ?";
            return getEntryList(sql, new RowMapper<LxmSignageError>() {
                @Override
                public LxmSignageError mapRow(ResultSet rs, int rowNum) throws SQLException {
                    LxmSignageError le = new LxmSignageError();
                    le.setNumber(rs.getString("number"));
                    le.setRectify_number(rs.getString("rectify_number"));
                    le.setUnit_name(rs.getString("unit_name"));
                    le.setName(rs.getString("name"));
                    le.setPhone(rs.getString("phone"));
                    le.setStatus(rs.getString("status"));
                    le.setExamine_date(rs.getString("examine_date"));
                    le.setReview_date(rs.getString("review_date"));
                    le.setReview_result(rs.getString("review_result"));
                    le.setAddress(rs.getString("address"));
                    le.setHidden_danger_desc(rs.getString("hidden_danger_desc"));
                    return le;

                }
            }, new Object[]{offset, (page - 1) * offset});
        } else {
            String sql = "select sr.*,se.rectify_number,se.unit_name,se.name,se.phone,se.status from sys_shop_error_copy1 se left join sys_signage_error_copy1 sr on sr.number=se.number where se.address like ? limit ? offset ?";
            return getEntryList(sql, new RowMapper<LxmSignageError>() {
                @Override
                public LxmSignageError mapRow(ResultSet rs, int rowNum) throws SQLException {
                    LxmSignageError le = new LxmSignageError();
                    le.setNumber(rs.getString("number"));
                    le.setRectify_number(rs.getString("rectify_number"));
                    le.setUnit_name(rs.getString("unit_name"));
                    le.setName(rs.getString("name"));
                    le.setPhone(rs.getString("phone"));
                    le.setStatus(rs.getString("status"));
                    le.setExamine_date(rs.getString("examine_date"));
                    le.setHidden_danger_desc(rs.getString("hidden_danger_desc"));
                    le.setReview_date(rs.getString("review_date"));
                    le.setReview_result(rs.getString("review_result"));
                    le.setAddress(rs.getString("address"));
                    return le;

                }
            }, new Object[]{"%" + address + "%", offset, (page - 1) * offset});
        }

    }

    @Override
    @Transactional
    public void updatePassword(String newPassword, String username) {
        String sql = "update sys_user_admin set password=? where user_name=?";
        update(sql, new Object[]{newPassword, username});
    }

    @Override
    public LxmSysUserAdmin getUserPassword(String password, String username) {
        String sql = "select * from sys_user_admin where user_name=? and password=?";
        return getEntry(sql, new LxmSysUserAdminRowMapper(), new Object[]{username, password});
    }

    @Override
    public LxmSysUserAdmin getUserInfoByPhone(String phone) {
        String sql = "select user_name,name,unit,phone,user_qx from sys_user_admin where phone=?";
        LxmSysUserAdmin entry = getEntry(sql, new LxmSysUserAdminRowMapper(), new Object[]{phone});
        String sql1 = "update sys_user_admin set password=123456 where phone=?";
        update(sql1, new Object[]{phone});
        return entry;
    }

    @Override
    public LxmSysUserAdmin getUserInfo(String username, String password) {
        String sql = "select user_name,name,unit,phone,user_qx from sys_user_admin where user_name=? and password=?";
        return getEntry(sql, new LxmSysUserAdminRowMapper(), new Object[]{username, password});
    }

    @Override
    public void deleteHpDianZhaoDianPaiInfo(String id) {
        String sql = "delete  from sys_shop_recruit_copy1 where id=?";
        update(sql, new Object[]{id});
    }

    @Override
    public void updateHpDianZhaoDianPaiInfo(SysShopRecruitStreet sr) {
        String sql = "update sys_shop_recruit_copy1 set road_name=?,unit_adress=?,serial_number=?,detail=?,unit_name=?,legal_person=?,legal_person_phone=?,name=?,phone=?,dz_number=?,spec=?,categary=?,status=?,dz_affiliation=? where id=?";
        update(sql, new Object[]{sr.getRoad_name(), sr.getUnit_adress(), sr.getSerial_number(), sr.getDetail(), sr.getUnit_name(), sr.getLegal_person(), sr.getLegal_person_phone(),
                sr.getName(), sr.getPhone(), sr.getDz_number(), sr.getSpec(), sr.getCategary(), sr.getStatus(), sr.getDz_affiliation(), sr.getId()
        });
    }

    @Override
    public void addHpDianZhaoDianPaiInfo(SysShopRecruitStreet sr) {
        String sql = "insert into sys_shop_recruit_copy1(road_name,unit_adress,serial_number,detail,unit_name,legal_person,legal_person_phone,name,phone,dz_number,spec,categary,status,dz_affiliation) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        update(sql, new Object[]{sr.getRoad_name(), sr.getUnit_adress(), sr.getSerial_number(), sr.getDetail(), sr.getUnit_name(), sr.getLegal_person(), sr.getLegal_person_phone(),
                sr.getName(), sr.getPhone(), sr.getDz_number(), sr.getSpec(), sr.getCategary(), sr.getStatus(), sr.getDz_affiliation()
        });
    }

    @Override
    public List<LxmElevatorInfo> getElevatorInfoByDeviceType(String deviceType) {
        String sql = "select manage_unit,supervise_person,ywh_name,ywh_person,ywh_phone,juwei,id,device_code,use_status,test_date,test_result,next_test_date,next_selftest_date,device_stop_date,putuse_date,register_type,device_type,point_monitor,use_unit,lat,lng,device_use_site,device_use_place,checkout_duty_uint,safe_administrator,safe_administrator_phone,service_unit_name,service_unit_phone,manufacture_date,checkout_type,device_rank,old_device_status  from elevator_info where device_type=?";
        return getEntryList(sql, new LxmElevatorInfoRowMapper(), new Object[]{deviceType});
    }

    @Override
    public List<LxmElevatorInfo> getElevatorInfoByUseStatus(String useStatus) {
        String sql = "select manage_unit,supervise_person,ywh_name,ywh_person,ywh_phone,juwei,id,device_code,use_status,test_date,test_result,next_test_date,next_selftest_date,device_stop_date,putuse_date,register_type,device_type,point_monitor,use_unit,lat,lng,device_use_site,device_use_place,checkout_duty_uint,safe_administrator,safe_administrator_phone,service_unit_name,service_unit_phone,manufacture_date,checkout_type,device_rank,old_device_status  from elevator_info where use_status=?";
        return getEntryList(sql, new LxmElevatorInfoRowMapper(), new Object[]{useStatus});
    }


}
